<?php

/*本文件负责实现与数据库相关的功能，具体内容包括：
 *连接数据库、登录、注册、获取用户信息
 */

//$conn在初次安装程序时替换生成
$conn = mysqli_connect('localhost', 'root', '', 'drxs');
if (!$conn) die('数据库连接失败');

/**
 * @return int '返回值：1表示创建成功'
 */
function InitDb(): int
{
    //创建数据表
    global $conn;
    //读取文件内容
    try {
        $sql = file_get_contents('drxs.sql');
        $sql = str_replace(PHP_EOL, '', $sql);
        $arr = explode(';', $sql);
        //执行sql语句
        foreach ($arr as $value) {
            @mysqli_query($conn, $value);
        }
    } catch (Exception $exception) {
    }
    return '1';
}

/**
 * @return int '返回值：1表示创建成功'
 */
function InitDbData(): int
{
    //导入示例数据
    global $conn;
    //读取文件内容
    try {
        $sql = file_get_contents('drxs_data.sql');
        $sql = str_replace(PHP_EOL, '', $sql);
        $arr = explode(';', $sql);
        //执行sql语句
        foreach ($arr as $value) {
            @mysqli_query($conn, $value);
        }
    } catch (Exception $exception) {
    }
    return 1;
}

/**
 * @return array|null
 */
function GetDbVersion()
{
    //获取数据库版本
    global $conn;
    $res = mysqli_query($conn, "select VERSION()");
    return mysqli_fetch_row($res);
}

/**
 * @param $login_email '登录电子邮件'
 * @param $login_pwd '用户密码'
 * @return int
 */
function UserLogin($login_email, $login_pwd): int
{
    //登录功能，返回值：1表示登录成功，2表示密码错误，3表示账号不存在
    $info = GetInfoByEmail($login_email);
    return $info ? (password_verify($login_pwd, $info['Password']) ? 1 : 2) : 3;
}

/**
 * @param $register_email '注册电子邮件'
 * @param $register_pwd '用户密码'
 * @return int  '返回值：1表示电子邮件已存在，2表示注册成功，3表示出错'
 */
function UserRegister($register_email, $register_pwd): int
{
    //注册功能
    global $conn;
    $check = GetInfoByEmail($register_email);
    if (!$check) {
        //数据库内没有当前电子邮件的账户，执行注册功能
        //根据数据库内用户数目，自动生成用户id
        $register_uid = mysqli_num_rows(mysqli_query($conn, "select * from drxs_user")) + 100000;
        //用户名由drxs_及随机数字组成
        $register_uname = sprintf("drxs_%s", rand(10000, 100000));
        //用户密码加密，由于使用了argon2id，因此版本要求PHP>=7.3
        $register_pwd = password_hash($register_pwd, PASSWORD_ARGON2ID);
        //记录注册日期
        $register_date = date('Y-m-d');
        $type = '学生';
        //将用户添加至数据库
        $sql_register = "INSERT INTO drxs_user(Id,Name,Password,Email,Date,Type)VALUES('$register_uid','$register_uname','$register_pwd','$register_email','$register_date','$type')";
        //初始化颜色等个性化配置
        $sql_ustyle = "INSERT into drxs_ustyle(Uid, ThemeColor, ThemeAccentColor, TableColor1, TableColor2, TableColor3)VALUES('$register_uid','mdui-theme-primary-white','mdui-theme-accent-blue','mdui-color-white','mdui-color-white','mdui-color-white')";
        return (mysqli_query($conn, $sql_register) && mysqli_query($conn, $sql_ustyle)) ? 2 : 3;
    } else return 1;    //账号已存在
}

/**
 * @param $sql '待执行的sql语句'
 * @return bool|mysqli_result
 */
function QueryDb($sql)
{
    //查询数据库
    global $conn;
    return mysqli_query($conn, $sql);
}

/**
 * @param $uid '用户Id'
 * @return array|null
 */
function GetInfoById($uid)
{
    //根据用户id获取用户信息
    global $conn;
    $sql_info = "select * from drxs_user where Id='$uid'";
    $info = mysqli_fetch_array(mysqli_query($conn, $sql_info));
    return !empty($info) ? $info : null;
}

/**
 * @param $email '用户电子邮件'
 * @return array|null
 */
function GetInfoByEmail($email)
{
    //根据电子邮件获取用户信息
    global $conn;
    $sql_info = "select * from drxs_user where Email='$email'";
    $info = mysqli_fetch_array(mysqli_query($conn, $sql_info));
    return !empty($info) ? $info : null;
}

/**
 * @param $table '表名'
 * @return bool|mysqli_result|null
 */
function GetAllInfo($table)
{
    //获取$table表中全部数据
    global $conn;
    $sql_get = "select * from $table";
    $info = mysqli_query($conn, $sql_get);
    return !empty($info) ? $info : null;
}

/**
 * @return bool|mysqli_result|null
 */
function QueryCourse()
{
    //从视图中查询课程信息
    global $conn;
    $sql_get = "select * from drxs_query_course_view order by CourseId";
    $info = mysqli_query($conn, $sql_get);
    return !empty($info) ? $info : null;
}


/**
 * @return bool|mysqli_result|null
 */
function QueryNotice()
{
    //从视图中查询公告信息
    global $conn;
    $sql_get = "select * from drxs_notice order by NoticeId DESC";
    return mysqli_query($conn, $sql_get);
}


/**
 * @param $value '学生Id'
 * @return bool|mysqli_result|null
 */
function QueryScore($value)
{
    //从视图中查询成绩信息
    global $conn;
    $sql_get = "select * from drxs_query_score_view where StudentId = $value order by CourseId DESC";
    return mysqli_query($conn, $sql_get);
}


/**
 * @param $teacher '教师Id'
 * @return bool|mysqli_result
 */
function QueryTeachingCourse($teacher)
{
    //查询教师当前讲授课程
    global $conn;
    $sql_get = "select * from drxs_query_course_view where TeacherId = $teacher order by CourseId";
    return mysqli_query($conn, $sql_get);
}


/**
 * @param $uid '用户Id'
 * @return array|null
 */
function GetUstyle($uid)
{
    //查询用户个性化配置
    global $conn;
    $sql_info = "select * from drxs_ustyle where Uid='$uid'";
    return mysqli_fetch_array(mysqli_query($conn, $sql_info));
}

/**
 * @param $uid '用户Id'
 * @param $name '待更改的用户姓名'
 * @return int '返回值，1表示成功，0表示失败'
 */
function ChangeName($uid, $name): int
{
    //修改用户姓名功能
    global $conn;
    $sql_update = "update drxs_user set Name = '$name' where Id = '$uid'";
    return mysqli_query($conn, $sql_update) ? 1 : 0;
}

/**
 * @param $uid '用户Id'
 * @param $email '待更改的电子邮件'
 * @return int '返回值，1表示成功，0表示失败'
 */
function ChangeEmail($uid, $email): int
{
    //修改电子邮件功能
    global $conn;
    $sql_update = "update drxs_user set Email = '$email' where Id = '$uid'";
    return mysqli_query($conn, $sql_update) ? 1 : 0;
}

/**
 * @param $uid '用户Id'
 * @param $sign '待更改的个性签名'
 * @return int '返回值，1表示成功，0表示失败'
 */
function ChangeSign($uid, $sign): int
{
    //修改个性签名功能
    global $conn;
    $sql_update = "update drxs_user set Signature = '$sign' where Id = '$uid'";
    return mysqli_query($conn, $sql_update) ? 1 : 0;
}

/**
 * @param $uid '用户Id'
 * @param $age '待更改的年龄'
 * @return int '返回值，1表示成功，0表示失败'
 */
function ChangeAge($uid, $age): int
{
    //修改个性签名功能
    global $conn;
    $sql_update = "update drxs_user set Age = '$age' where Id = '$uid'";
    return mysqli_query($conn, $sql_update) ? 1 : 0;
}

/**
 * @param $uid '用户Id'
 * @param $sex '待更改的性别'
 * @return int '返回值，1表示成功，0表示失败'
 */
function ChangeSex($uid, $sex): int
{
    //修改性别功能
    global $conn;
    $sql_update = "update drxs_user set Sex = '$sex' where Id = '$uid'";
    return mysqli_query($conn, $sql_update) ? 1 : 0;
}

/**
 * @param $uid '用户Id'
 * @param $tel '待更改的电话号码'
 * @return int '返回值，1表示成功，0表示失败'
 */
function ChangeTel($uid, $tel): int
{
    //修改电话功能
    global $conn;
    $sql_update = "update drxs_user set Tel = '$tel' where Id = '$uid'";
    return mysqli_query($conn, $sql_update) ? 1 : 0;
}

/**
 * @param $uid '用户Id'
 * @param $pwd '待更改的密码'
 * @return int '返回值，1表示成功，0表示失败'
 */
function ChangePassword($uid, $pwd): int
{
    //修改密码功能
    global $conn;
    $pwd = password_hash($pwd, PASSWORD_ARGON2ID);
    $sql_update = "update drxs_user set Password = '$pwd' where Id = '$uid'";
    return mysqli_query($conn, $sql_update) ? 1 : 0;
}

/**
 * @param $uid '用户Id'
 * @param $theme '待更改的主题色'
 * @return int '返回值，1表示成功，0表示失败'
 */
function ChangeTheme($uid, $theme): int
{
    //修改主题色功能
    global $conn;
    $sql_update = "update drxs_ustyle set ThemeColor = '$theme' where Uid = '$uid'";
    return mysqli_query($conn, $sql_update) ? 1 : 0;
}

/**
 * @param $uid '用户Id'
 * @param $accent '待更改的主题强调色'
 * @return int '返回值，1表示成功，0表示失败'
 */
function ChangeAccent($uid, $accent): int
{
    //修改主题强调色功能
    global $conn;
    $sql_update = "update drxs_ustyle set ThemeAccentColor = '$accent' where Uid = '$uid'";
    return mysqli_query($conn, $sql_update) ? 1 : 0;
}

/**
 * @param $uid '用户Id'
 * @param $val_1 '表头配色'
 * @param $val_2 '表格数据行配色1'
 * @param $val_3 '表格数据行配色2'
 * @return int '返回值，1表示成功，0表示失败'
 */
function ChangeTableColor($uid, $val_1, $val_2, $val_3): int
{
    //修改表格配色功能
    global $conn;
    $sql_update = "update drxs_ustyle set TableColor1='$val_1',TableColor2='$val_2',TableColor3='$val_3' where Uid = '$uid'";
    return mysqli_query($conn, $sql_update) ? 1 : 0;
}

/**
 * @param $name '课程名称'
 * @param $teacher '教师Id'
 * @param $classroom '教室Id'
 * @param $hour '学时'
 * @param $credit '学分'
 * @param $time '上课时间'
 * @return int  '返回值，1表示成功，0表示失败'
 */
function AddCourse($name, $teacher, $classroom, $hour, $credit, $time): int
{
    //添加新课程功能
    global $conn;
    $id = mysqli_num_rows(mysqli_query($conn, "select * from drxs_course")) + 10000;
    $sql_course = "INSERT INTO drxs_course(CourseId,CourseName,Hour,Credit,TeacherId,ClassroomId,Time)VALUES('$id','$name','$hour','$credit','$teacher','$classroom','$time')";
    return mysqli_query($conn, $sql_course) ? 1 : 0;
}

/**
 * @param $course '课程Id'
 * @param $student '学生Id'
 * @param $score '成绩'
 * @return int '返回值，1表示成功，0表示失败'
 */
function AddScore($course, $student, $score): int
{
    //添加成绩功能
    global $conn;
    $sql_score = "INSERT INTO drxs_score(CourseId,StudentId,Score)VALUES('$course','$student','$score')";
    return mysqli_query($conn, $sql_score) ? 1 : 0;
}

/**
 * @param $id '公告Id'
 * @param $time '发布时间'
 * @param $user '发布者'
 * @param $title '公告标题'
 * @param $content '公告内容'
 * @return int '返回值，1表示成功，0表示失败'
 */
function ReleaseNotice($id, $time, $user, $title, $content): int
{
    //发布公告功能
    global $conn;
    $sql_score = "INSERT INTO drxs_notice(NoticeId, NoticeTitle, NoticeContent, Time, PublisherId)VALUES('$id','$title','$content','$time','$user')";
    return mysqli_query($conn, $sql_score) ? 1 : 0;
}


/**
 * @param $uemail '用户电子邮件'
 */
function LoginInit($uemail)
{
    //登录初始化，记录用户信息并保存至session
    $info = GetInfoByEmail($uemail);
    $_SESSION['uid'] = $info['Id'];
    $_SESSION['utype'] = $info['Type'];
    //获取用户个性化配置
    $style = GetUstyle($info['Id']);
    $_SESSION['theme_color'] = $style['ThemeColor'];
    $_SESSION['theme_accent_color'] = $style['ThemeAccentColor'];
    $_SESSION['table_color_1'] = $style['TableColor1'];
    $_SESSION['table_color_2'] = $style['TableColor2'];
    $_SESSION['table_color_3'] = $style['TableColor3'];
}

/**
 * @param $token '自动登录Token口令'
 */
function AutoLoginCheck($token)
{
    //自动登录判断
    global $conn;
    $sql = "select * from drxs_autologin where Token = '$token'";
    $info = mysqli_query($conn, $sql);
    return mysqli_num_rows($info) > 0 ? mysqli_fetch_array($info)['UEmail'] : 'none';
}

/**
 * @param $uemail '用户电子邮件'
 * @param $token '自动登录Token口令'
 */
function InsertToken($uemail, $token)
{
    global $conn;
    if (mysqli_num_rows(mysqli_query($conn, "select * from drxs_autologin where UEmail = '$uemail'")) > 0) {
        mysqli_query($conn, "delete from drxs_autologin where UEmail = '$uemail'");
    }
    $sql = "insert into drxs_autologin(UEmail, Token)VALUES('$uemail','$token')";
    mysqli_query($conn, $sql);

}